/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.fourtwelve.Data;

/**
 *
 * @author ASUS_M5A97_ADMIN
 */
public interface DBConstants {
    final String DB_NAME = "412_BANK";
    final String DRIVER_NAME = "sun.jdbc.odbc.JdbcOdbcDriver";
    final String PROTOCOL = "jdbc:odbc:";
    
    final String MEMBER_TABLE = "Member";
    final String MEM_COL_MEMBER_ID = "MEMBER_ID";
    final String MEM_COL_MEMBER_F_NAME = "MEMBER_F_NAME";
    final String MEM_COL_MEMBER_L_NAME = "MEMBER_L_NAME";
    final String MEM_COL_MEMBER_USERNAME = "MEMBER_USERNAME";
    final String MEM_COL_MEMBER_ADDRESS = "MEMBER_ADDRESS";
    final String MEM_COL_MEMBER_ADDRESS_2 = "MEMBER_ADDRESS_2";
    final String MEM_COL_MEMBER_CITY = "MEMBER_CITY";
    final String MEM_COL_MEMBER_STATE = "MEMBER_STATE";
    
    final String ACCOUNT_TABLE = "Account";
    final String ACC_COL_ACCOUNT_ID = "ACCOUNT_ID";
    final String ACC_COL_ACCOUNT_NUMBER = "ACCOUNT_NUMBER";
    final String ACC_COL_ACCOUNT_BALANCE = "ACCOUNT_BALANCE";
    final String ACC_COL_ACCOUNT_TYPE = "ACCOUNT_TYPE";    
    
    final String MEMBER_ACCOUNT_TABLE = "MemberAccount";
    final String MEM_ACC_COL_MEMBER_ID = "MEMBER_ID";
    final String MEM_ACC_COL_ACCOUNT_ID = "ACCOUNT_ID";
    
    final String MEMBER_CREDENTIALS_TABLE = "MemberCredentials";
    final String MEM_CRED_COL_MEMBER_ID = "MEMBER_ID";
    final String MEM_CRED_COL_HASHED_PASS = "HASHED_PASS";
    final String MEM_CRED_COL_SALT = "SALT";
    final String MEM_CRED_COL_FUNCTION = "FUNCTION";
    
    final String TRANSACTIONS_TABLE = "Transactions";
    final String TRANS_COL_TRANSACTION_ID = "TRANSACTION_ID";
    final String TRANS_COL_TRANSACTION_TYPE_ID = "TRANSACTION_TYPE_ID";
    final String TRANS_COL_TRANSACTION_DESCRIPTION = "TRANSACTION_DESCRIPTION";
    final String TRANS_COL_TRANSACTION_VALUE = "TRANSACTION_VALUE";
    final String TRANS_COL_ACCOUNT_ID = "ACCOUNT_ID";
    
    final String TRANSACTION_TYPES_TABLE = "TransactionTypes";
    final String TRANS_TYPES_COL_TRANSACTION_TYPE_ID = "TRANSACTION_TYPE_ID";
    final String TRANS_TYPES_COL_TRANSACTION_TYPE = "TRANSACTION_TYPE";
    
    final String ACCOUNT_TYPES_TABLE = "AccountTypes";
    final String ACCOUNT_TYPES_COL_ACCOUNT_TYPE_ID = "ACCOUNT_TYPE_ID";
    final String ACCOUNT_TYPES_COL_ACCOUNT_TYPE_DESCRIPTION = "ACCOUNT_TYPE_DESCRIPTION";
        
    final String GET_USER = "SELECT " + MEM_COL_MEMBER_ID + " FROM "+ MEMBER_TABLE + 
            " WHERE " + MEM_COL_MEMBER_USERNAME + " = ?;";
    final String GET_EXISTING_USER = "SELECT " + MEM_COL_MEMBER_ID + " FROM "+ MEMBER_TABLE + 
            " WHERE " + MEM_COL_MEMBER_F_NAME + " = ? AND " + MEM_COL_MEMBER_L_NAME + " = ?;";
    final String GET_USER_ID = "SELECT " + MEM_COL_MEMBER_ID + " FROM "+ MEMBER_TABLE + 
            " WHERE " + MEM_COL_MEMBER_USERNAME + " = ?;";
    final String GET_ACCOUNT = "SELECT " + ACC_COL_ACCOUNT_ID + ", " + ACC_COL_ACCOUNT_TYPE +
            ", " + ACC_COL_ACCOUNT_NUMBER + " FROM " + ACCOUNT_TABLE + " WHERE " + 
            ACC_COL_ACCOUNT_NUMBER + " = ?;";
    final String GET_CREDENTIALS = "SELECT " + MEM_CRED_COL_HASHED_PASS + ", " + MEM_CRED_COL_SALT +
           " FROM " + MEMBER_CREDENTIALS_TABLE + " WHERE " + MEM_CRED_COL_MEMBER_ID + " = ?;";
    final String GET_USER_ACCOUNTS = "SELECT " + ACCOUNT_TABLE + "." + ACC_COL_ACCOUNT_ID + ", " +
            ACCOUNT_TYPES_TABLE + "." + ACCOUNT_TYPES_COL_ACCOUNT_TYPE_ID + ", " +
            MEMBER_ACCOUNT_TABLE + "." + MEM_ACC_COL_MEMBER_ID + ", " +
            MEMBER_ACCOUNT_TABLE + "." + MEM_ACC_COL_ACCOUNT_ID + ", " +
            ACCOUNT_TABLE + "." + ACC_COL_ACCOUNT_NUMBER  + ", " +
            ACCOUNT_TYPES_TABLE + "." + ACCOUNT_TYPES_COL_ACCOUNT_TYPE_DESCRIPTION + ", " +
            ACCOUNT_TABLE + "." + ACC_COL_ACCOUNT_BALANCE +
            "\nFROM (" + ACCOUNT_TYPES_TABLE + " INNER JOIN " + ACCOUNT_TABLE + " ON " +
            ACCOUNT_TYPES_TABLE + ".[" + ACCOUNT_TYPES_COL_ACCOUNT_TYPE_ID + "] = " +
            ACCOUNT_TABLE + ".[" + ACC_COL_ACCOUNT_TYPE + "]) INNER JOIN " + MEMBER_ACCOUNT_TABLE + 
            " ON " + ACCOUNT_TABLE + ".[" + ACC_COL_ACCOUNT_ID + "] = " + MEMBER_ACCOUNT_TABLE + ".[" + 
            MEM_ACC_COL_ACCOUNT_ID + "]\n" + "WHERE (" + MEMBER_ACCOUNT_TABLE + "." + MEM_ACC_COL_MEMBER_ID
            + " = ?);";
    final String GET_ACCOUNT_TRANSACTION = "SELECT " + TRANSACTIONS_TABLE + "." + 
            TRANS_COL_TRANSACTION_ID + ", " + TRANSACTIONS_TABLE + "." + TRANS_COL_TRANSACTION_TYPE_ID
            + ", " + TRANSACTIONS_TABLE + "." + TRANS_COL_TRANSACTION_DESCRIPTION + ", " +
            TRANSACTIONS_TABLE + "." + TRANS_COL_TRANSACTION_VALUE + ", " +
            TRANSACTION_TYPES_TABLE + "." + TRANS_TYPES_COL_TRANSACTION_TYPE +
            "\nFROM (" + TRANSACTIONS_TABLE + " INNER JOIN " + TRANSACTION_TYPES_TABLE + " ON " +
            TRANSACTIONS_TABLE + ".[" + TRANS_COL_TRANSACTION_TYPE_ID + "] = " +
            TRANSACTION_TYPES_TABLE + ".[" + TRANS_TYPES_COL_TRANSACTION_TYPE_ID + "])\n" + 
            "WHERE (" + TRANSACTIONS_TABLE + "." + TRANS_COL_ACCOUNT_ID
            + " = ?)\nORDER BY " +  TRANSACTIONS_TABLE + ".[" + TRANS_COL_TRANSACTION_ID + "] DESC;";
//    final String GET_TRANSACTION_TYPE_DESC = "SELECT " + TRANS_TYPES_COL_TRANSACTION_TYPE + 
//            " FROM " + TRANSACTION_TYPES_TABLE + " WHERE " + TRANS_TYPES_COL_TRANSACTION_TYPE_ID
//            + " = ?;";
    
    final String INSERT_USER = "INSERT INTO " + MEMBER_TABLE + " (" + MEM_COL_MEMBER_USERNAME + 
            "," + MEM_COL_MEMBER_F_NAME + "," + MEM_COL_MEMBER_L_NAME + "," + 
            MEM_COL_MEMBER_ADDRESS + "," + MEM_COL_MEMBER_ADDRESS_2  + "," +
            MEM_COL_MEMBER_CITY  + "," + MEM_COL_MEMBER_STATE + 
            ")\nVALUES (?, ?, ?, ?, ?, ?, ?);";
    final String INSERT_USER_PASS = "INSERT INTO " + MEMBER_CREDENTIALS_TABLE + 
            " (" + MEM_CRED_COL_MEMBER_ID + "," + MEM_CRED_COL_HASHED_PASS + 
            "," + MEM_CRED_COL_SALT +  ")\nVALUES (?, ?, ?);";
    final String INSERT_MAP_USER_ACCT = "INSERT INTO " + MEMBER_ACCOUNT_TABLE + " (" + MEM_ACC_COL_MEMBER_ID + 
            "," + MEM_ACC_COL_ACCOUNT_ID + ")\nVALUES (?, ?);";
    final String INSERT_TRANSACTION = "INSERT INTO " + TRANSACTIONS_TABLE + 
            " (" + TRANS_COL_TRANSACTION_TYPE_ID + "," + TRANS_COL_TRANSACTION_DESCRIPTION +
            "," + TRANS_COL_TRANSACTION_VALUE + "," + TRANS_COL_ACCOUNT_ID + 
            ")\nVALUES (?, ?, ?, ?);";
    
    final String UPDATE_USER = "UPDATE " + MEMBER_TABLE + "\nSET " + MEM_COL_MEMBER_USERNAME + 
            " = ?, " + MEM_COL_MEMBER_F_NAME + " = ?, " + MEM_COL_MEMBER_L_NAME + " = ?, " + 
            MEM_COL_MEMBER_ADDRESS + " = ?, " + MEM_COL_MEMBER_ADDRESS_2  + " = ?, " +
            MEM_COL_MEMBER_CITY + " = ?, " + MEM_COL_MEMBER_STATE + " = ?\nWHERE " +
            MEM_COL_MEMBER_ID + " = ?;";
    final String UPDATE_ACCOUNT = "UPDATE " + ACCOUNT_TABLE + "\nSET " + 
            ACC_COL_ACCOUNT_BALANCE + " = ?\nWHERE " + ACC_COL_ACCOUNT_ID + " = ?;";

    final byte PRIMARY_ACCOUNT_MASK = (1 << 0);
    final byte CHECKING_ACCOUNT_MASK = (1 << 1);
    final byte SAVINGS_ACCOUNT_MASK = (1 << 2);
    final byte LOAN_ACCOUNT_MASK = (1 << 3);
    
    final int CHECKING_CREDIT = 1;
    final int CHECKING_DEBIT = 2;
    final int SAVINGS_CREDIT = 3;
    final int SAVINGS_DEBIT = 4;
    final int LOAN_CREDIT = 5;
    final int LOAN_DEBIT = 6;
    
    
}